A database typically includes one or more database tables for storing data values. To access the data in the database, an application typically issues a database query to a Database Management System (DBMS) that retrieves the data from the database. The DBMS may be structured to support different types of operations from the requesting application server to retrieve, add, modify and/or delete data being stored in the database. The application then interprets the data retrieved from the data server and transforms the data into a format that can be processed by the application.
Typically, data is represented and manipulated differently in the application and data servers. The application may be written using a high level programming language that provides a high level of abstraction suited for developing business applications, such as developing custom reports and interfaces. Such high-level programming languages may allow dynamic selections of values and conditions for database access during run-time. Dynamic selections may be provided by the user during run-time to define the query to execute.
The data server, on the other hand, is typically structured to support operations that are programmed using a static query language that requires all variables and conditions to be defined or known at compilation (prior to run-time). One example of such static query language is static Structured Query Language (“SQL”), which has evolved as the standard language used by database systems to interpret what, where and how data is to be stored and manipulated. Static SQL statements do not change from execution to execution. The full text of static SQL statements is known at compilation.
To integrate application and data servers, it may be necessary to translate the application code to the query language that is compatible with the database server. However, it is difficult to perform such translation due to the complexity of existing business logic that is encoded in the application. In addition, it is also not easy to transform the data stored in the application's custom data types and structures to the database's native data types and structures.
One common method of integrating the application and database servers is to use dynamic SQL. Dynamic SQL is an extension of SQL that is executed by the database server and allows the data server to consider user input that is received during run-time in constructing an SQL query statement. Compared to static SQL, it provides more flexibility and allows the use of variables that may not be supported by static SQL.
However, executing dynamic SQL in the data server may have a negative impact on security. Since the variables are not known during compilation, verification to ensure that the query statement references valid database objects or necessary privileges are in place to access the database objects cannot be performed. In addition, opportunities for optimizations are limited, and the query statement is potentially recompiled each time the statement is executed.
Therefore, there is a need for an improved integration framework that addresses the above-mentioned challenges.